package com.aba.bms.system;

import java.sql.*;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
 
public class ReportGenerator {
    private ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
 
    public void startDailyReportTask() {
        scheduler.scheduleAtFixedRate(() -> {
            try {
                generateDailyReport();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }, 0, 1, TimeUnit.DAYS); // 每天执行一次
    }
 
    private void generateDailyReport() throws Exception {
        // 获取当前日期
        java.sql.Date today = new java.sql.Date((long) System.currentTimeMillis());
 
        // 统计借阅次数
        String borrowCountSql = "SELECT COUNT(*) AS total FROM borrow_records WHERE borrow_date BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)";
        // 统计归还次数
        String returnCountSql = "SELECT COUNT(*) AS total FROM borrow_records WHERE return_date BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)";
        // 统计活跃用户数（当天有借阅或归还记录的用户）
        String activeUsersSql = "SELECT COUNT(DISTINCT user_id) AS total FROM borrow_records WHERE borrow_date BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY) OR return_date BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)";
        // 统计最受欢迎图书（当天借阅次数最多的图书）
        String popularBookSql = "SELECT b.title, COUNT(*) AS borrow_count FROM borrow_records br JOIN books b ON br.book_id = b.book_id WHERE br.borrow_date BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY) GROUP BY br.book_id ORDER BY borrow_count DESC LIMIT 1";
 
        try (Connection conn = ConnectionDatabaseForMySQLUseJdbc.getConnection()) {
            // 执行统计查询
            int totalBorrows = getCount(conn, borrowCountSql, today);
            int totalReturns = getCount(conn, returnCountSql, today);
            int activeUsers = getActiveUsers(conn, activeUsersSql, today);
            String mostPopularBook = getMostPopularBook(conn, popularBookSql, today);
 
            // 存储统计结果
            String insertSql = "INSERT INTO borrow_statistics (stat_date, total_borrows, total_returns, active_users, most_popular_book) VALUES (?, ?, ?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                stmt.setDate(1, today);
                stmt.setInt(2, totalBorrows);
                stmt.setInt(3, totalReturns);
                stmt.setInt(4, activeUsers);
                stmt.setString(5, mostPopularBook);
                stmt.executeUpdate();
            }
        }
    }
 
    private int getCount(Connection conn, String sql, java.sql.Date date) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDate(1, date);
            stmt.setDate(2, date);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("total");
            }
        }
        return 0;
    }
 
    private int getActiveUsers(Connection conn, String sql, java.sql.Date date) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDate(1, date);
            stmt.setDate(2, date);
            stmt.setDate(3, date);
            stmt.setDate(4, date);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("total");
            }
        }
        return 0;
    }
 
    private String getMostPopularBook(Connection conn, String sql, java.sql.Date date) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDate(1, date);
            stmt.setDate(2, date);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString("title");
            }
        }
        return "无";
    }
}